﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace 聚欣订单分析.PublicClass
{
    public class PLSQLClass
    {
        public PLSQLClass()
        {
        }

        private string ConnectionString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["JXDDConnectionString"].ConnectionString;
        }

        /// <summary>
        /// 处理SQL语句返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable SqlBind(string sql)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConnectionString();
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            SqlDataAdapter sdap = new SqlDataAdapter();
            sdap.SelectCommand = com;
            DataTable dt = new DataTable();
            sdap.Fill(dt);
            con.Close();//关闭数据库
            return dt;
        }

        // 用事务
        public void ExecuteSqlWithGoUseTran(String sql)
        {
           // int effectedRows = 0;
            string connectionString = ConnectionString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;  
                try
                {
                    //注： 此处以 go 来分割字符串  
                    String[] sqlArr = Regex.Split(sql.Trim(), "go", RegexOptions.IgnoreCase);
                    foreach (string strsql in sqlArr)
                    {
                        if (strsql.Trim().Length > 1 && strsql.Trim() != "\r\n")
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }  
            }
        }

        /// <summary>
        /// 处理SQL语句返回第一行第一列
        /// </summary>
        /// <param name="sq"></param>
        /// <returns></returns>
        public string SqlReaderData(string sql)
        {
            string ss = "";
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConnectionString();
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            SqlDataReader myReader = com.ExecuteReader();
            if (myReader.Read())
            {
                ss = myReader[0].ToString();
            }
            con.Close();
            return ss;
        }

        /// <summary>
        /// 处理SQL语句返回影响行数
        /// </summary>
        /// <param name="sq"></param>
        /// <returns></returns>
        public int SqlGetData(string sql)
        {
            string ss = "";
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConnectionString();
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            int row = com.ExecuteNonQuery();
            con.Close();
            return row;
        }
    }
}
